package com.skyline.base.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataRetrievalFailureException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import com.skyline.base.dao.BaseDao;
import com.skyline.common.bean.Page;

/**
 * DAO基础类，封装了公共的DAO方法。如分页等的实现
 * 
 * @author wuqh
 * @version 1.00
 * */
@Repository("baseDao")
public class BaseDaoImpl implements BaseDao {
	@Autowired
	protected JdbcTemplate jdbcTemplate;
	protected Map<String, String> formattedSqls;

	/**
	 * 获取分页结果
	 * 
	 * @param sql
	 * @param page
	 * @param rowMapper
	 * @param args
	 */
	protected <T> List<T> getPaginationResult(String sql, Page page, RowMapper<T> rowMapper, Object... args) {
		int total = getCountResult(sql, args);
		page.setTotal(total);
		if (total <= 0) {
			return new ArrayList<T>();
		}
		int startIndex = page.getStartIndex();
		String pageSql = sql + " limit " + startIndex + "," + page.getSize();
		return jdbcTemplate.query(pageSql, rowMapper, args);
	}

	/**
	 * 统计结果总数
	 * 
	 * @param sql
	 * @param args
	 */
	protected int getCountResult(String sql, Object... args) {
		String countSql = "select count(1) from (" + sql + ") cntTbl;";
		return jdbcTemplate.queryForInt(countSql, args);
	}

	/**
	 * 插入数据并返回ID
	 * 
	 * @param sql
	 * @param args
	 */
	protected long insertWithIdReturn(final String sql, final Object... args) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
				for (int i = 1; i <= args.length; i++) {
					ps.setObject(i, args[i - 1]);
				}
				return ps;
			}

		}, keyHolder);
		return keyHolder.getKey().longValue();
	}

	/**
	 * 批量更新
	 * 
	 * @param sql
	 * @param argsList
	 * 
	 * */
	protected int[] batchUpdate(final String sql, final List<Object[]> argsList) {
		Assert.notEmpty(argsList, "args can not be empty while batch insert");
		return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				Object[] args = argsList.get(i);
				int length = args.length;
				for (int j = 1; j <= length; j++) {
					ps.setObject(j, args[j - 1]);
				}
			}

			@Override
			public int getBatchSize() {
				return argsList.size();
			}
		});
	}

	/**
	 * 批量插入数据并返回ID（参考Spring批量插入，使用非InterruptibleBatchPreparedStatementSetter实现）
	 * 
	 * @param sql
	 * @param argsList
	 */
	@SuppressWarnings("unchecked")
	protected <T extends Number> List<T> batchInsertWithIdReturn(final String sql, final List<Object[]> argsList) {
		Assert.notEmpty(argsList, "args can not be empty while batch insert");
		final KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.execute(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
				return ps;
			}
		}, new PreparedStatementCallback<int[]>() {
			private void setValues(PreparedStatement ps, int i) throws SQLException {
				Object[] args = argsList.get(i);
				int length = args.length;
				for (int j = 1; j <= length; j++) {
					ps.setObject(j, args[j - 1]);
				}
			}

			private int[] processResult(PreparedStatement ps, int[] rows) throws SQLException {
				List<Map<String, Object>> generatedKeys = keyHolder.getKeyList();
				generatedKeys.clear();
				ResultSet keys = ps.getGeneratedKeys();
				if (keys != null) {
					try {
						RowMapperResultSetExtractor<Map<String, Object>> rse = new RowMapperResultSetExtractor<Map<String, Object>>(
								new ColumnMapRowMapper(), rows.length);
						generatedKeys.addAll(rse.extractData(keys));
					} finally {
						JdbcUtils.closeResultSet(keys);
					}
				}
				return rows;
			}

			@Override
			public int[] doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				int batchSize = argsList.size();
				if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
					for (int i = 0; i < batchSize; i++) {
						setValues(ps, i);
						ps.addBatch();
					}
					int[] rows = ps.executeBatch();
					return processResult(ps, rows);
				} else {
					List<Integer> rowsAffected = new ArrayList<Integer>();
					for (int i = 0; i < batchSize; i++) {
						setValues(ps, i);
						rowsAffected.add(ps.executeUpdate());
					}
					int[] rowsAffectedArray = new int[rowsAffected.size()];
					for (int i = 0; i < rowsAffectedArray.length; i++) {
						rowsAffectedArray[i] = rowsAffected.get(i);
					}
					return processResult(ps, rowsAffectedArray);
				}
			}
		});

		List<T> keys = new ArrayList<T>(argsList.size());
		List<Map<String, Object>> generatedKeys = keyHolder.getKeyList();
		int length = generatedKeys.size();
		for (int i = 0; i < length; i++) {
			Iterator<Object> keyIter = generatedKeys.get(i).values().iterator();
			if (keyIter.hasNext()) {
				Object key = keyIter.next();
				if (!(key instanceof Number)) {
					throw new DataRetrievalFailureException("The generated key is not of a supported numeric type. " + "Unable to cast ["
							+ (key != null ? key.getClass().getName() : null) + "] to [" + Number.class.getName() + "]");
				}
				keys.add((T) key);
			}
		}
		return keys;
	}

	/**
	 * 动态生成SQL中的表名
	 * 
	 * @param sqlNeedFormat
	 *            需要生成表名的原sql,格式如“select * from {0} where colum<>1”
	 * @param tableName
	 *            表名用于替换sqlNeedFormat中的{0}，格式“tableName”
	 * @return 最后生成的SQL,如参数中给的例子就是“select * from tableName where colum<>1”
	 * */
	protected String genSql(String sqlNeedFormat, String tableName) {
		// 采用懒加载，会有多线程并发时有概率多次创建sqlFormatters，把一些之前用用过的MessageFormat冲掉的问题，不过数量应该不会太大，可以忽略
		if (formattedSqls == null) {
			formattedSqls = new HashMap<String, String>();
		}
		String key = tableName + sqlNeedFormat;
		String sql = formattedSqls.get(key);
		if (sql == null) {
			sql = MessageFormat.format(sqlNeedFormat, tableName);
			formattedSqls.put(key, sql);
		}
		return sql;

	}
}
